using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// Summary description for DBConnectionTool
/// </summary>
public class DBConnectionTool
{
    protected SqlConnection cn;
    public DBConnectionTool()
	{
		//
		// TODO: Add constructor logic here
		//
        string strcn = ConfigurationManager.ConnectionStrings["strcn"].ToString();
        cn = new SqlConnection(strcn);        
	}   
    public void CloseCn()
    {
        if (cn.State == ConnectionState.Open)
        {
            cn.Close();
        }
    }
    public void OpenCn()
    {
        if (cn.State == ConnectionState.Closed)
        {
            cn.Open();
        }
    }
    public void setModelDropdownList(DropDownList ddl, string dataTextField, string dataValueField, DataTable table)
    {
        ddl.DataSource = table.DefaultView;
        ddl.DataTextField = dataTextField;
        ddl.DataValueField = dataValueField;
        ddl.DataBind();
    }
    public void setModelWebListBox(System.Web.UI.WebControls.ListBox lst, string dataTextField, string dataValueField, DataTable table)
    {
        lst.DataSource = table.DefaultView;
        lst.DataTextField = dataTextField;
        lst.DataValueField = dataValueField;
        lst.DataBind();
    }
    public void SetModelGridView(GridView gvw, DataTable dt, string[] key)
    {
        gvw.DataSource = dt;
        gvw.DataKeyNames = key;
        gvw.DataBind();
    }
    public int ExecSQL(string sql)
    {
        int result = -1;
        OpenCn();
        try
        {
            SqlCommand cmd = new SqlCommand(sql, cn);
            result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            CloseCn();
        }
        catch (Exception)
        {
            return -1;
        }
        return result;
    }
    public object ExecSQLscalar(string sql)
    {
        object result;
        OpenCn();
        try
        {
            SqlCommand cmd = new SqlCommand(sql, this.cn);
            result = cmd.ExecuteScalar();
            CloseCn();
            return result;
        }
        catch (Exception)
        {
            CloseCn();
            return null; }

    }
}
